<template>
	<view class="page">
		<button @click="openTable">打开数据库</button>
		<button @click="closeTable">关闭数据库</button>
		<button @click="createTable">创建表</button>
		<button @click="insertTable">插入数据</button>
		<button @click="deleteTable">删除数据</button>
		<button @click="editTable">修改数据</button>
		<button @click="getTable">查询数据</button>
		
		<view class="box">
			<view class="item" v-for="(item, index) in list" :key="item.id">{{ index }} - {{ item.name }} - {{ item.age }}</view>
		</view>
	</view>
</template>

<script setup>
	import DB from '@/utils/sqlite.js';

	import {
		ref
	} from 'vue';
	import {
		onShow
	} from '@dcloudio/uni-app';
	
	onShow(() => {
		getTable();
	})

	const tbName = ref('home_chat');
	const list = ref([]);

	// 判断数据库是否打开
	function isOpen() {
		return plus.sqlite.isOpenDatabase({
			name: DB.dbName,
			path: DB.dbPath
		});
	}

	// 打开数据库
	function openTable() {
		let open = isOpen();
		if (open) {
			uni.showToast({
				title: '数据库已处于开启状态',
				icon: 'none'
			})
		} else {
			plus.sqlite.openDatabase({
				name: DB.dbName,
				path: DB.dbPath,
				success: res => {
					uni.showToast({
						title: '数据库打开成功',
						icon: 'none'
					})
				},
				fail: err => {
					uni.showToast({
						title: '数据库打开失败',
						icon: 'none'
					})
				}
			})
		}
	}

	// 关闭数据库
	function closeTable() {
		let open = isOpen();
		if (open) {
			plus.sqlite.closeDatabase({
				name: DB.dbName,
				success: res => {
					uni.showToast({
						title: '数据库关闭成功',
						icon: 'none'
					})
				},
				fail: err => {
					uni.showToast({
						title: '数据库关闭失败',
						icon: 'none'
					})
				}
			})
		} else {
			uni.showToast({
				title: '数据库已处于关闭状态',
				icon: 'none'
			})
		}
	}

	// 创建表
	function createTable() {
		let open = isOpen();
		if (open) {
			let sql = `create table if not exists home_chat (id integer primary key autoincrement, name text, age int)`;
			plus.sqlite.executeSql({
				name: DB.dbName,
				sql,
				success: res => {
					uni.showToast({
						title: `创建${tbName.value}成功`,
						icon: 'none'
					})
				},
				fail: err => {
					uni.showToast({
						title: `创建${tbName.value}失败`,
						icon: 'none'
					})
				}
			})
		} else {
			uni.showToast({
				title: '数据库未打开',
				icon: 'none'
			})
		}
	}

	// 插入数据
	function insertTable() {
		let open = isOpen();
		if (open) {
			let tHead = 'name, age';
			let arr = [{
				name: 'swk',
				age: 16
			}, {
				name: 'zbj',
				age: 22
			}];
			arr.map(item => {
				let sql = `insert into ${tbName.value} (${tHead}) values ('${item.name}', '${item.age}')`;
				plus.sqlite.executeSql({
					name: DB.dbName,
					sql,
					success: res => {
						uni.showToast({
							title: '数据添加成功',
							icon: 'none'
						})
						getTable();
					},
					fail: err => {
						console.log(err);
						uni.showToast({
							title: '数据添加失败',
							icon: 'none'
						})
					}
				})
			})
		} else {
			uni.showToast({
				title: '数据库未打开',
				icon: 'none'
			})
		}
	}
	
	// 删除数据
	function deleteTable() {
		let open = isOpen();
		if (open) {
			let sql = `delete from ${tbName.value}`;
			plus.sqlite.executeSql({
				name: DB.dbName,
				sql,
				success: res => {
					uni.showToast({
						title: '数据删除成功',
						icon: 'none'
					});
					getTable();
				},
				fail: err => {
					uni.showToast({
						title: '数据删除失败',
						icon: 'none'
					})
				}
			})
		} else {
			uni.showToast({
				title: '数据库未打开',
				icon: 'none'
			})
		}
	}
	
	// 修改数据
	function editTable() {
		let open = isOpen();
		if (open) {
			let sql = `update ${tbName.value} set name = '孙悟空', age = '长大一岁' where name = 'swk'`;
			plus.sqlite.executeSql({
				name: DB.dbName,
				sql,
				success: res => {
					uni.showToast({
						title: '数据修改成功',
						icon: 'none'
					});
					getTable();
				},
				fail: err => {
					uni.showToast({
						title: '数据修改失败',
						icon: 'none'
					});
				}
			})
		} else {
			uni.showToast({
				title: '数据库未打开',
				icon: 'none'
			})
		}
	}

	// 查询数据
	function getTable() {
		let open = isOpen();
		if (open) {
			let sql = `SELECT * FROM ${tbName.value}`;
			plus.sqlite.selectSql({
				name: DB.dbName,
				sql,
				success: res => {
					uni.showToast({
						title: '数据查询成功',
						icon: 'none'
					});
					list.value = res;
				},
				fail: err => {
					uni.showToast({
						title: '数据查询失败',
						icon: 'none'
					})
				}
			})
		} else {
			uni.showToast({
				title: '数据库未打开',
				icon: 'none'
			})
		}
	}
</script>